package com.blue.waffle.iwc.database;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.badlogic.gdx.Gdx;
import com.blue.waffle.iwc.domains.Difficulty;
import com.blue.waffle.iwc.domains.helpers.DifficultyHelper;
import com.blue.waffle.iwc.domains.helpers.LevelHelper;
import com.blue.waffle.iwc.domains.helpers.WordHelper;

public class DesktopSqliteActionResolver implements SqliteActionResolver {

	private Connection connection; 
	private static final String DATABASE_URI = "jdbc:sqlite:IWC.db";
	private static final String WORD_LIST_FILE="data/wordlist.csv";
	private static final String CREATE_STATEMENT = "CREATE TABLE IF NOT EXISTS difficulty (id INTEGER PRIMARY KEY, name TEXT, description TEXT);"
			+ "CREATE TABLE IF NOT EXISTS achievement (id INTEGER PRIMARY KEY, description TEXT, picture TEXT, completed NUMERIC, title TEXT);"
			+ "CREATE TABLE IF NOT EXISTS difficulty_level (difficulty_id NUMERIC, level_id NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS level (word_id NUMERIC, id INTEGER PRIMARY KEY, title TEXT UNIQUE ON CONFLICT IGNORE,difficulty_id NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS profile (id INTEGER PRIMARY KEY, name TEXT, level_random_seed NUMERIC, last_played_level_index NUMERIC, current_difficulty NUMERIC, attempts NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS profile_achievement (id INTEGER PRIMARY KEY, profile_id , achievement_id , completed_at NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS profile_level (playtime NUMERIC, profile_id NUMERIC, level_id NUMERIC, score NUMERIC, time_completed NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS word (id INTEGER PRIMARY KEY, word TEXT UNIQUE ON CONFLICT IGNORE, picture TEXT, definition TEXT);";

	private static final String DROP_STATEMENT = "DROP TABLE IF EXISTS achievement;"
			+ "DROP TABLE IF EXISTS profile_achievement;"
			+ "DROP TABLE IF EXISTS difficulty;"
			+ "DROP TABLE IF EXISTS difficulty_level ;"
			+ "DROP TABLE IF EXISTS level ;"
			+ "DROP TABLE IF EXISTS profile ;"
			+ "DROP TABLE IF EXISTS profile_level ;"
			+ "DROP TABLE IF EXISTS word ;";
	
	public Connection getConnection() {
		try {
			Class.forName("org.sqlite.JDBC");
			connection=DriverManager.getConnection(DATABASE_URI);
			connection.setAutoCommit(true);
			return connection;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public void init() throws SQLException {
		getConnection();
		Statement st = connection.createStatement();
		ResultSet rs = st.executeQuery("SELECT count(name) FROM sqlite_master WHERE type='table'");
		if (rs.next()) {
			System.out.println("Drop database!");
			st.executeUpdate(DROP_STATEMENT);
		}
		
		st.executeUpdate(CREATE_STATEMENT);
		System.out.println("Database is created");
		
		rs.close();
		
		DifficultyHelper difficultyHelper=new DifficultyHelper(connection);
		Difficulty easy=new Difficulty("Easy","Easy Difficulty");
		Difficulty normal=new Difficulty("Normal","Normal Difficulty");
		Difficulty hard=new Difficulty("Hard","Hard Difficulty");
		
		difficultyHelper.insert(easy);
		difficultyHelper.insert(normal);
		difficultyHelper.insert(hard);
				
		WordHelper wordHelper=new WordHelper(connection);
		try {
			Reader file=Gdx.files.internal(WORD_LIST_FILE).reader();
			wordHelper.insertWordsFromCSV(file);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		LevelHelper levelHelper=new LevelHelper(connection);
		levelHelper.insertLevels(wordHelper.selectAll(), difficultyHelper.selectAll());
		
		connection.close();
	}

}
